Smart Buttons
Controls/XML Elements

Read From Excel <ReadFromExcel>

The ReadFromExcel control reads information from a specified Excel file. This control can:

  • Read information from single cell.

  • Read information from single column or row, which results in a Simple collection.

  • Read information from two columns or two rows, which results in a Value**Key collection.

Important!

If you are using cloud storage, for example, Google Drive or Microsoft OneDrive, be sure your file is closed when the script runs. Otherwise, an error message may be displayed: Failed to open Workbook.

Configuring

You can configure ReadFromExcel in the Smart Buttons Editor using either the Read from Excel Editor or XML.

Configuring with the Read from Excel Editor

To configure with the Read from Excel Editor:

  1. Open the Smart Buttons Editor to create a script.

  2. Click Read from Excel <ReadFromExcel> to display the Read from Excel Editor.

  3. Configure the attributes.

    Attribute

    Description

    Excel path

    Browse or enter the path for the Excel document you want to read information from.
    Variable name Enter the variable name that stores the result of this control.
    Sheet name (optional) If the information you want to read is stored in an Excel Sheet other than the default Sheet, enter the Sheet name.
    Read by Select if to read information by column or by row. If you want to read only one cell select Cell.
    Read Row If Row is selected in the Read by field, this field is activated. Enter the row number to be read. For example, 255 (only numbers are allowed).
    Read Column If Column is selected in the Read by field, this field is activated. Enter the column number to be read. For example, BA (only letters allowed).
    Read Key from row

    To read information from two rows, enter the second row number. For example: 256.

    A collection Value**Key type is created (where the Value comes from ReadRow field and the Key comes from the Read Key from row field.
    Read Key from column

    To read information from two columns, enter the second column number. For example: 256.

    A collection Value**Key type is created (where the Value comes from ReadColumn field and the Key comes from the Read Key from column field.
    Read Column from/ Read Column to

    If Column is selected in the Read by field, specify the column range to be read. Enter the first column to be read in Read Column from and enter the last column in range in Read Column to.

    If you leave this field blank, the system reads all columns until it finds first empty cell. This is useful if you want to keep adding information to the next columns.
    Read Row from/ Read Row to

    If Row is selected in the Read by field, specify the row range to be read. Enter the first row to be read in Read Row from and enter the last row in range in Read Row to.

    If you leave this field blank, the system reads all rows until it finds first empty cell. This is useful if you want to keep adding information to the next rows.
    Skip empty cell If the Excel file can contain empty rows, or columns in between rows or columns you want to read, select “Skip empty cell” so that all empty rows and columns will be ignored.
    Add another Excel block

    if you want to create multiple variables with information from multiple columns/rows from the same Excel file, then add as many block as you need, for example, you want to read information from column A to variable [CustomerCode] and information from column B to variable: [CustomerNumber] andinformation from column C to variable [CustomerCountry] then add 3 block as per below.

    Scroll down:

    All the information from multiple columns to multiple variables from the same excel file are being read in 1 transaction which shorten the operation time to minimum.

  4. Click SUBMIT.

Configuring with XML

If you want to enter the XML code directly in the Smart Buttons Editor, use the following structure for the XML element.

Tip! You can add the XML code directly to the editor by dragging and dropping the blue button for this control.

Copy 
<ReadFromExcel VarName="PreviousTicketPrice" ExcelPath="C:\MyDatabase.xlsx" SheetName="Prices2021" 
ReadBy="Column" Column="A" Row="60:79" ColumnKey="B" />
Attributes

Attribute

Description
VarName

Specify the variable name. For example: CustomerNames.
ExcelPath

Specify the file location.
SheetName

Optional. Use if you want to read data from a specific Excel sheet.
ReadBy

Read by Column, Row, or Cell
Column

When Row is selected in @ReadBy, enter the column range in format AB:AC. Separate the first column from the last column using a colon ":"
ColumnKey Optional. Enter the second column name, for example, "D" (only letters are allowed) to read. Creates the Key part of the element in Value**Key collection.
Row When Column is selected in @ReadBy, enter the row raneg in format 25:367. Separate the first row from the last row with a colon ":"
RowKey Optional. Enter the second row name, for example, "321" (only numerics allowed) to read. Creates the Key part of the element in Value **Key collection.
Cell

Enter the cell address to read. For example: E7,

CellKey Enter the second Cell address to read. For example: H3. Creates the Key part of the element in Value **Key collection.
SkipEmptyCells

True

False

Examples

The following examples using the Read from Excel Editor to configure the controls.

Example 1: Read Customer Names Only

Read customer names from Column K in the Excel spreadsheet.

  1. Open the Smart Buttons Editor.

  2. Click Read from Excel <ReadFromExcel> to display the Read from Excel Editor.

  3. Fill out the Read From Excel Editor.

  4. Click SUBMIT to generate the XML code.

    Copy 
    <ReadFromExcel VarName="CustomerName" ExcelPath="C:\MyDocuments\CustomerDatabase.xlsx" 
    ReadBy="Column" Column="K" Row="3:11" />

  5. Name the script by typing script name in the “Button name” text field.

  6. Add ShowMessage for testing purposes.

    Now if you want to test your script you may want to add <ShowMessage> XML element to display information that script is reading from excel. Inside the <ShowMessage> tag enter the Variable name used in <ReadFromExcel> (Variable always in square brackets)

  7. Click on the script to test it

    Script has read information from Excel and displayed it as a message.

Example 2: Read Customer Names and Customer Numbers

Read customer names (Column K) and related customer numbers (Column L) from the Excel spreadsheet.

  1. Open the Smart Buttons Editor.

  2. Click Read from Excel <ReadFromExcel> to display the Read from Excel Editor.

  3. Fill out the Read From Excel Editor.

    • Select Read Key from column and enter second column you want to read (L)

    • Select Skip empty cells because there may be empty rows, and you want script to ignore any empty rows (for example: row 7).

  1. Click SUBMIT to generate the XML code.

    Copy 
    <ReadFromExcel VarName="CustomerNamesAndNumbers" ExcelPath="C:\MyDocuments\CustomerDatabase.xlsx" 
    SheetName="CustomersAK" ReadBy="Column" Column="K" Row="4:11" ColumnKey="L" SkipEmptyCells="True" />

  2. Add ShowMessage for testing purposes.

    Now if you want to test your script you may want to add <ShowMessage> XML element to display information that the script is reading from Excel. Inside the <ShowMessage> tag enter the Variable name used in <ReadFromExcel> (when adding Variable as a value of other XML element always use square brackets)

  3. Name the script by typing script name in the “Button name” text field.

  4. Click on the script to test it

    Script has read both Customer Name and Customer Numbers from Excel and displayed it as a message. This is a Value**Key Collection, where Value=Customer Name, Key=CustomerNumber.